

/**
	Author		: Rohan
	Date		: May 23rd 2004
	Description	: This proc is used to save or update the sales order details in CT database
			  while migrating the sales orders from Epicore to CT. For updating the sales order
			  details this proc internally calls the sp_change_sales_order_proc procedure.
			
*/
ALTER PROC sp_ct_sls_epi_migration_save_sales_order

@SALES_ORDER_NO       VARCHAR(50),
@CUST_ID              INT,
@CUST_PO_NO           VARCHAR(50),
@CONTACT_PERSON       VARCHAR(50),
@CONTACT_PERSON_PH_NO VARCHAR(50),
@SALES_PERSON         VARCHAR(50),
@SHIP_METHOD          VARCHAR(50),
@SHIPPING_INSTR       TEXT,
@SHIPPING_TERMS       VARCHAR(50),
@SALES_TAX_TYPE       VARCHAR(50),
@SALES_ORDER_DATE     VARCHAR(10),
@EPICORE_SHIP_CODE    VARCHAR(8),
@SHIP_ADDRESS1        VARCHAR(50),
@SHIP_ADDRESS2        VARCHAR(50),
@SHIP_ADDRESS3        VARCHAR(50),
@SHIP_ADDRESS4        VARCHAR(50),
@SHIP_CITY            VARCHAR(50),
@SHIP_STATE           VARCHAR(50),
@SHIP_ZIP             VARCHAR(50),
@SHIP_COUNTRY         VARCHAR(50),
@SHIP_PHONE           VARCHAR(50),
@SHIP_FAX             VARCHAR(50),
@SHIP_EMAIL           VARCHAR(50),
@SHIP_CONTACT_PERSON  VARCHAR(50),
@SHIP_CELL_NUMBER     VARCHAR(50),
@EPICORE_SO_STATUS    CHAR(1),
@EPICORE_HOLD         INT,
@SUB_TOTAL            FLOAT,
@SALES_TAX_AMT        FLOAT,
@FREIGHT_AMT          FLOAT,
@MISC_AMT             FLOAT,
@USER_ID              VARCHAR(50),
@USER_NAME            VARCHAR(50),
@SALES_ORDER_ID       INT OUT,
@INSERTED             INT OUT --SPECIFIES IF THE CUSTOMER IS INSERTED OR UPDATED

AS
BEGIN

--DECLARATIONS
DECLARE @SHIP_ACCT      VARCHAR(50)
DECLARE @COMMENTS       VARCHAR(255)
DECLARE @END_USER       VARCHAR(50)
DECLARE @REGIONAL_REP   VARCHAR(50)
DECLARE @DELIVERY_TERMS VARCHAR(50)
DECLARE @MISC_NAME      VARCHAR(50)
DECLARE @CUST_NAME      VARCHAR(50)
DECLARE @PHONE          VARCHAR(50)
DECLARE @FAX            VARCHAR(50)
DECLARE @EMAIL          VARCHAR(50)
DECLARE @CELL_NUMBER    VARCHAR(50)
DECLARE @ADDRESS        VARCHAR(50)
DECLARE @BILLING_CONTACT_PERSON VARCHAR(50)
DECLARE @SHIPPING_CONTACT_PERSON VARCHAR(50)
DECLARE @STATUS_FLAG      INT
DECLARE @CANCELLED        INT
DECLARE @SHIPPED          INT
DECLARE @PENDING          INT
DECLARE @BILLING_ADDR_ID  INT
DECLARE @SHIPPING_ADDR_ID INT

--INITIALISATIONS
SET @SHIP_ACCT = ''

--ADDED BY ROHAN ON JUNE 1ST 2004
IF( @CONTACT_PERSON IS NULL )
BEGIN
	SET @CONTACT_PERSON = ''
END

IF( @CONTACT_PERSON_PH_NO IS NULL )
BEGIN
	SET @CONTACT_PERSON_PH_NO = ''
END

--ADD CODE FOR INSERTING THE SHIPPINGMEHTODS, PAYMENTS TERMS ETC.. WHICH ARE NOT EXISTING IN CT
EXEC sp_ct_sls_insert_sales_order_related_if_not_existing @CUST_ID, @SHIP_METHOD, @SHIP_ACCT, @SALES_TAX_TYPE, @SALES_PERSON, @SHIPPING_TERMS, @CONTACT_PERSON, @CONTACT_PERSON_PH_NO 

--RETREIVINT THE SHIPPING ACCOUNT CORRESPONDING TO THE GIVEN SHIPPING METHOD
SELECT @SHIP_ACCT = SHIPPING_ACCOUNT FROM SHIPPING_METHODS_TABLE NOLOCK WHERE SHIPPING_METHOD = @SHIP_METHOD AND SM_CUSTOMER_ID = @CUST_ID

--RETREIVING THE CUSTOMER NAME
SELECT @CUST_NAME = C_NAME FROM CUSTOMER NOLOCK WHERE CUSTOMER_ID = @CUST_ID

--RETREIVING THE SHIPPING ADDRESS ID BASED ON THE EPICORE SHIP CODE
select @SHIPPING_ADDR_ID = Address_ID from Address NOLOCK where ( Address_ID in ( select Address_Id from Customer_Shipping_Address_Table where customer_id = @CUST_ID ) or Address_ID = ( select C_Default_Shipping_Address_ID from customer where customer_id = @CUST_ID ) or Address_ID = ( select C_Default_Billing_Address_ID from customer where customer_id = @CUST_ID ) ) and Epicore_Ship_Code = @EPICORE_SHIP_CODE

IF( @@ROWCOUNT = 0 )
BEGIN
	INSERT INTO Address ( Address_1, Address_2, City, State, Zipcode, Country, Phone_Number, Fax_Number, Email, Contact_Person, Cell_Number, Address_3, Address_4, Epicore_Ship_Code) 
		     VALUES ( @SHIP_ADDRESS1, @SHIP_ADDRESS2, @SHIP_CITY, @SHIP_STATE, @SHIP_ZIP, @SHIP_COUNTRY, @SHIP_PHONE, @SHIP_FAX, @SHIP_EMAIL, @SHIP_CONTACT_PERSON, @SHIP_CELL_NUMBER, @SHIP_ADDRESS3, @SHIP_ADDRESS4, @EPICORE_SHIP_CODE )
	
	SET @SHIPPING_ADDR_ID = @@IDENTITY
END

--SETTING THE PROPERTIES OF THE SALES ORDER LIKE CANCELLED, SHIPPED, PENDING BASED ON THE EPICORE SO STATUS
SET @SHIPPED   = 1
SET @PENDING   = 1

--IF THE EPICORE STATUS IS EITHER VOID OR CANCELLED THEN SET THE CANCELLED PROPERTY AS TRUE
IF( @EPICORE_SO_STATUS = 'V' OR @EPICORE_SO_STATUS = 'X' )
BEGIN
	SET @CANCELLED = 0
END
ELSE
BEGIN
	SET @CANCELLED = 1
END

--IF THERE IS A HOLD ON THE SALES ORDER IN EPICORE THEN SETTING THE PENDING AS TRUE
IF( @EPICORE_HOLD = 1 )
BEGIN
	SET @PENDING = 0
END
ELSE
BEGIN
	SET @PENDING = 1
END

--CHECKIG IF THE GIVEN SALES ORDER ID IS EXISTING
SELECT @SALES_ORDER_ID = SALESORDER_ID FROM SALES_ORDER NOLOCK WHERE SALESORDER_NO = @SALES_ORDER_NO

IF( @@ROWCOUNT = 0 )
BEGIN
	--INSERTING SALES ORDER DETAILS
	SET @INSERTED = 1
		
	SET @SHIPPED = 1 --SETTING THE SHIPPED VALUE AS FALSE
		
	--SELECTING THE BILLING ADDRESS ID OF THE CUSTOMER
	SELECT @BILLING_ADDR_ID = C_Default_Billing_Address_ID FROM CUSTOMER NOLOCK WHERE CUSTOMER_ID = @CUST_ID	

	INSERT INTO Sales_Order( Customer_ID, SO_Customer_PO_Number, SO_Contact_Person, SO_Sales_Person, SO_Shipping_Method, SO_Shipping_Account, SO_Billing_Address_ID, SO_Shipping_Address_ID, SO_Comments, SO_Shipping_Instructions, SO_EndUser, SO_Status, SO_Booking_date, SO_Is_Sample, SO_Is_Cancel, SO_Is_Pending, SO_Is_Shipped, SO_Entered_By_ID, SO_Entered_By_Name, SO_Sales_Tax_Type, SO_Sub_Total, SO_Sales_Tax, SO_Frieght_Tax, SO_Misc,SalesOrder_No,Regional_Rep, Shipping_Terms, Delivery_terms, SO_Misc_Name, timestamp, SO_Billing_Contact_Person, SO_Shipping_Contact_Person) 
        	         VALUES( @CUST_ID, @CUST_PO_NO, @CONTACT_PERSON, @SALES_PERSON, @SHIP_METHOD, @SHIP_ACCT, @BILLING_ADDR_ID, @SHIPPING_ADDR_ID, '', @SHIPPING_INSTR, '', 'PENDING', @SALES_ORDER_DATE, 1, @CANCELLED, @PENDING, @SHIPPED, @USER_ID, @USER_NAME, @SALES_TAX_TYPE, @SUB_TOTAL, @SALES_TAX_AMT, @FREIGHT_AMT, @MISC_AMT, @SALES_ORDER_NO, '', @SHIPPING_TERMS, '', '', CURRENT_TIMESTAMP, '', '' )
	
	--RETREIVING THE NEW SALES ORDER ID
	SET @SALES_ORDER_ID = @@IDENTITY
	
	--INSERTING SALES ORDER IN THE CHANGE SALES ORDER TABLE WITH THE CHANGE NUMBER AS 0			
	INSERT INTO Change_Sales_Order( SalesOrder_ID, ChangeNo, Customer_ID, CSO_Customer_PO_Number, CSO_Contact_Person, CSO_Sales_Person, CSO_Shipping_Method, CSO_Shipping_Account, CSO_Billing_Address_ID, CSO_Shipping_Address_ID, CSO_Comments, CSO_Shipping_Instructions, CSO_EndUser, CSO_Status, CSO_Booking_date, CSO_Is_Sample, CSO_Is_Cancel, CSO_Is_Pending, CSO_Is_Shipped, CSO_Entered_By_ID, CSO_Entered_By_Name, CSO_Sales_Tax_Type, CSO_Sub_Total, CSO_Sales_Tax, CSO_Frieght_Tax, CSO_Misc,Regional_Rep, Shipping_Terms, Delivery_terms, CSO_Misc_Name, timestamp) 
        	                VALUES( @SALES_ORDER_ID, 0, @CUST_ID, @CUST_PO_NO, @CONTACT_PERSON, @SALES_PERSON, @SHIP_METHOD, @SHIP_ACCT, @BILLING_ADDR_ID, @SHIPPING_ADDR_ID, '', @SHIPPING_INSTR, '', 'PENDING', @SALES_ORDER_DATE, 1, @CANCELLED, @PENDING, @SHIPPED, @USER_ID, @USER_NAME, @SALES_TAX_TYPE, @SUB_TOTAL, @SALES_TAX_AMT, @FREIGHT_AMT, @MISC_AMT, '', @SHIPPING_TERMS, '', '', CURRENT_TIMESTAMP )
		
	--UPDATING THE CONTACT PERSON DETAILS
	IF( UPPER( RTRIM( @CONTACT_PERSON ) ) = UPPER( RTRIM( @CUST_NAME ) ) ) 
	BEGIN
		INSERT INTO SALES_ORDER_ADDRESS( SALESORDER_ID, PHONE_NUMBER ) VALUES( @SALES_ORDER_ID, @CONTACT_PERSON_PH_NO )
	END
	ELSE
	BEGIN
		INSERT INTO SALES_ORDER_ADDRESS( SALESORDER_ID, PHONE_NUMBER, FAX_NUMBER, EMAIL ) SELECT @SALES_ORDER_ID, PHONE_NUMBER, FAX_NUMBER, EMAIL FROM ADDRESS WHERE ADDRESS_ID = @BILLING_ADDR_ID
	END	

END -- END OF CONDITION FOR INSERTING THE SALES ORDER
ELSE
BEGIN
	--UPDATING THE SALES ORDER DETAILS
	SET @INSERTED = 0
			
	--RETREIVING THE DETAILS PERTAINING TO THE SALES ORDER WHICH ARE SAVED IN CHIPTRAC 	
	SELECT  @BILLING_ADDR_ID         = SO_Billing_Address_ID,
		@SHIPPED                 = SO_Is_Shipped,
		@COMMENTS                = SO_Comments, 
		@END_USER                = SO_EndUser,
		@MISC_NAME               = SO_Misc_Name,
		@REGIONAL_REP            = Regional_Rep, 
		@DELIVERY_TERMS          = Delivery_Terms, 
        	@BILLING_CONTACT_PERSON  = SO_Billing_Contact_Person , 
		@SHIPPING_CONTACT_PERSON = SO_Shipping_Contact_Person  FROM Sales_Order NOLOCK WHERE SalesOrder_ID = @SALES_ORDER_ID

	SELECT  @PHONE = Phone_Number ,@FAX = Fax_Number , @EMAIL = Email , @CELL_NUMBER = Cell_Number FROM Address NOLOCK WHERE Address_Id = @BILLING_ADDR_ID
	SET @ADDRESS = '' --NOT SURE ABT WHERE TO GET THE DETAILS FROM	

	EXEC sp_change_sales_order_proc @CUST_PO_NO, @CONTACT_PERSON, 
					@SALES_PERSON, @SHIP_METHOD, 
					@SHIP_ACCT, @SHIPPING_ADDR_ID, 
					@COMMENTS, @SHIPPING_INSTR, 
					@END_USER, @SALES_TAX_TYPE, 
					@SUB_TOTAL, @SALES_TAX_AMT, 
					@FREIGHT_AMT, @MISC_AMT, 
					@SALES_ORDER_NO, @REGIONAL_REP, 
					@SHIPPING_TERMS, @DELIVERY_TERMS, 
					@SALES_ORDER_DATE, @SALES_ORDER_ID, 
					'', @PHONE, 
					@FAX, @EMAIL, 
					@CELL_NUMBER, @ADDRESS, 
					@STATUS_FLAG OUT, @MISC_NAME, 
					@BILLING_ADDR_ID, @BILLING_CONTACT_PERSON, @SHIPPING_CONTACT_PERSON


END -- END OF CONDITION FOR UPDATE THE SALES ORDER DETAILS

END --END OF PROC
